﻿using System.Data;
using System.Data.SqlClient;
using System.Reflection;


public static class SqlHelper
{
    #region connstr

    //如果需要外部指定连接字符串，就只需要定义一个公共变量即可
    //public static string connstr = "";

    //1.如果使用Properties栏目的Settings.settings来设置
    //static string connstr = Properties.Settings.Default.SqlConnection;

    //2.WinForm项目，请添加对“System.Configuration”的引用
    //2.1.对配置文件connectionStrings节进行读取
    //static string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnStr"].ConnectionString;

    //2.2.对配置文件appSettings节进行读取
    static string connstr = System.Configuration.ConfigurationManager.AppSettings["DBConnStr"];

    //3.Webform使用本文件的项目对“System.Web”，“System.Web.Configuration”添加引用
    //static string connstr = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["DBConnStr"].ConnectionString; 


    #endregion

    #region ExecuteDataSet +1
    /// <summary>
    /// ExecuteDataSet
    /// </summary>
    /// <param name="sql">查询字符串</param>
    /// <returns>DataTable</returns>
    public static DataSet ExecuteDataSet(string sql, params SqlParameter[] paramenters)
    {
        return ExecuteDataSet(CommandType.Text, sql, paramenters);
    }
    /// <summary>
    /// ExecuteDataSet
    /// </summary>
    /// <param name="sql">查询字符串</param>
    /// <param name="paramenters">params SqlParameter[]</param>
    /// <returns>DataSet</returns>
    public static DataSet ExecuteDataSet(CommandType type, string sql, params SqlParameter[] paramenters)
    {
        using (DataSet dataset = new DataSet())
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connstr))
            {
                adapter.SelectCommand.CommandType = type;
                adapter.SelectCommand.Parameters.AddRange(paramenters);
                adapter.Fill(dataset);
                return dataset;
            }
        }
    }
    #endregion

    #region ExecuteScalar +1
    /// <summary>
    /// ExecuteScalar
    /// </summary>
    /// <param name="sql">查询字符串</param>
    /// <returns>object</returns>
    public static object ExecuteScalar(string sql, params SqlParameter[] paramenters)
    {
        return ExecuteScalar(CommandType.Text, sql, paramenters);
    }
    /// <summary>
    /// ExecuteScalar
    /// </summary>
    /// <param name="sql">查询字符串</param>
    /// <param name="paramenters">params SqlParameter[]</param>
    /// <returns>object</returns>
    public static object ExecuteScalar(CommandType type, string sql, params SqlParameter[] paramenters)
    {
        using (SqlConnection conn = new SqlConnection(connstr))
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn) { CommandType = type })
            {
                cmd.Parameters.AddRange(paramenters);
                conn.Open();
                return cmd.ExecuteScalar();
            }
        }
    }
    #endregion

    #region ExecuteNonQuery +1
    /// <summary>
    /// ExecuteNonQuery
    /// </summary>
    /// <param name="sql">查询字符串</param>
    /// <returns>int</returns>
    public static int ExecuteNonQuery(string sql, params SqlParameter[] paramenters)
    {
        return ExecuteNonQuery(CommandType.Text, sql, paramenters);
    }
    /// <summary>
    /// ExecuteNonQuery
    /// </summary>
    /// <param name="sql">查询字符串</param>
    /// <param name="paramenters">params SqlParameter[]</param>
    /// <returns>int</returns>
    public static int ExecuteNonQuery(CommandType type, string sql, params SqlParameter[] paramenters)
    {
        using (SqlConnection conn = new SqlConnection(connstr))
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn) { CommandType = type })
            {
                cmd.Parameters.AddRange(paramenters);
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }
    }
    #endregion

    #region 把包含 对象 数据的DataRow转换成 实体对象(泛型反射方式)
    /// <summary>
    /// 把包含 对象 数据的DataRow转换成 实体对象(泛型反射方式)
    /// </summary>
    /// <typeparam name="T">源对象类型</typeparam>
    /// <param name="dr">包含 对象 数据的DataRow</param>
    /// <returns></returns>
    public static T Row2Model<T>(DataRow dr) where T : new()
    {
        T model = new T();
        foreach (PropertyInfo p in (typeof(T)).GetProperties())
        {
            if (dr[p.Name] is System.DBNull) { break; }
            p.SetValue(model, dr[p.Name], null);
        }
        return model;
    }
    #endregion
}

